package com.hgy.fxui.dao;

import com.hgy.fxui.model.Drug;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface DrugDao {
    /**
     * 获取药品库存量排名前N的数据
     */
    @Select("SELECT name, stock FROM drug ORDER BY stock DESC LIMIT #{limit}")
    List<Map<String, Object>> getTopDrugsByStock(int limit);

    /**
     * 获取药品分类统计
     */
    @Select("SELECT manufacturer as category, COUNT(*) as count FROM drug GROUP BY manufacturer")
    List<Map<String, Object>> getDrugCategoryDistribution();

    /**
     * 获取药品总数量
     */
    @Select("SELECT COUNT(*) FROM drug")
    int getTotalDrugsCount();

    /**
     * 获取库存预警药品数量（库存小于或等于预警值的药品数）
     */
    @Select("SELECT COUNT(*) FROM drug WHERE stock <= 100")
    int getLowStockDrugsCount();

    /**
     * 获取药品库存总金额
     */
    @Select("SELECT SUM(stock * price) as total_value FROM drug")
    Double getTotalInventoryValue();
    
    @Select("SELECT name, stock FROM drug ORDER BY stock DESC LIMIT 5")
    List<Map<String, Object>> getTop5Inventory();
    
    @Select("SELECT * FROM drug")
    List<Drug> getAllDrugs();
    
    @Insert("INSERT INTO drug(name, specification, manufacturer, price, stock, create_time) VALUES(#{name}, #{specification}, #{manufacturer}, #{price}, #{stock}, now())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Drug drug);
    
    @Update("UPDATE drug SET name=#{name}, specification=#{specification}, manufacturer=#{manufacturer}, price=#{price}, stock=#{stock} WHERE id=#{id}")
    int update(Drug drug);
    
    @Delete("DELETE FROM drug WHERE id=#{id}")
    int delete(Integer id);
}
